package com.example.qxfw.hbqx.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.qxfw.common.entity.zy.AirPollution;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

@Mapper
@DS("service")
public interface AirPollutionMapper extends BaseMapper<AirPollution> {
    /**
     *根据时间段查询空气逐小时实况
     * @param observtimeStart 起始时间
     * @param observtimeEnd 终止时间
     * @return
     */
    @Select("SELECT observtime, aqi, fractpm2p5, fractpm10, fractco, fractO3, fractO38h, fractso2, fractno2 " +
            "FROM airpollution " +
            "WHERE observtime >= #{observtimeStart}  AND observtime <= #{observtimeEnd} " +
            "ORDER BY observtime ")
    List<AirPollution> selectByTimePeriodAirPollution(Date observtimeStart, Date observtimeEnd);

    /**
     * 根据站点和开始时间查询空气逐小时实况
     * @param observtimeStart 开始时间
     * @return
     */
    @Select("SELECT observtime, aqi, fractpm2p5, fractpm10, fractco, fractO3, fractO38h, fractso2, fractno2 " +
            "FROM airpollution " +
            "WHERE observtime = #{observtimeStart} " +
            "ORDER BY observtime desc")
    List<AirPollution> selectByTimeOfAirPollution(Date observtimeStart);

    /**
     *降序查询所有观测时间，并只显示一条
     * @return
     */
    @Select("SELECT observtime " +
            "FROM airpollution " +
            "ORDER BY observtime DESC " +
            "LIMIT 1")
    List<AirPollution> selectByObservtime();

    /**
     *从 AirPollution 表中获取除了 StationID 为 'stationid' 的最近3个不重复的 observTime 时间字段，并按降序排序。
     * @param stationid 站点id
     * @return
     */
    @Select("SELECT DISTINCT TOP 3 observtime " +
            "FROM airpollution " +
            "WHERE stationid NOT LIKE concat('%',#{stationid},'%') " +
            "ORDER BY observtime DESC ")
    List<AirPollution> selectByNotStationid(String stationid);

    /**
     * 获取在规定时间段中非stationid的观测时间,升序
     * @param observtimeStart 观测时间起点
     * @param observtimeEnd 观测时间终点
     * @param stationid 站点id
     * @return
     */
    @Select("SELECT DISTINCT observtime " +
            "FROM airpollution " +
            "WHERE observtime >= #{observtimeStart} AND observtime <= #{observtimeEnd} AND stationid NOT LIKE concat('%',#{stationid},'%') " +
            "ORDER BY observtime ASC ")
    List<AirPollution> selectByObservtimeAndNotStationid(Date observtimeStart, Date observtimeEnd, String stationid);

    /**
     * 根据元素和时间段获取空气质量日统计表信息
     * @param observtimeStart 开始时间
     * @param observtimeEnd 结束时间
     * @param element 字段
     * @return
     */
    @Select("SELECT stationid, observtime, longitude, latitude, altitude, stationname, ${element}, city, county " +
            "FROM airpollution " +
            "WHERE observtime >= #{observtimeStart} AND observtime <= #{observtimeEnd} ")
    List<AirPollution> getByTimePeriodAndElementOfAirPollution(Date observtimeStart, Date observtimeEnd, String element);


    /**
     * 根据元素和时间段获取空气质量日统计表信息
     * @param observtime 开始时间
     * @param element 字段
     * @return
     */
    @Select("SELECT stationid, observtime, longitude, latitude, altitude, stationname, ${element}, city, county  " +
            "FROM airpollution " +
            "WHERE observtime = #{observtime} ")
    List<AirPollution> getByTimeAndElementOfAirPollution(Date observtime, String element);



}
